﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate;
using Domain.Entities;
using NHibernate.Transform;
using NHibernate.Criterion;
using System.Collections;

namespace Data
{
    public class Operations
    {
        protected ISession Session { get; set; }
        public Operations(ISession session)
        {
            Session = session;
        }

        /// <summary>
        /// 保存Customer对象
        /// </summary>
        /// <param name="customer"></param>
        public void CreateCustomer(Customer customer)
        {
            Session.Save(customer);
            Session.Flush();
        }

        /// <summary>
        /// 根据ID获取Customer对象
        /// </summary>
        /// <param name="customerId"></param>
        /// <returns></returns>
        public Customer GetCustomerById(int customerId)
        {
            //Customer c = Session.Get<Customer>(1);
            return Session.Get<Customer>(customerId);
        }

        /// <summary>
        /// 获取所有Customer实例
        /// </summary>
        /// <returns></returns>
        public IList<Customer> From()
        {
            return Session.CreateQuery("from Customer").List<Customer>();
        }

        /// <summary>
        /// 根据firstname获取Customer对象组合[利用命名参数:(:name)]
        /// </summary>
        /// <param name="firstname"></param>
        /// <returns></returns>
        public IList<Customer> GetCustomersByFirstName(string firstname)
        {
            return Session.CreateQuery("from Customer c where c.Firstname=:fn").SetString("fn", firstname).List<Customer>();
        }

        /// <summary>
        /// 获取Customer对象集合，最大5条
        /// </summary>
        /// <returns></returns>
        public IList<Customer> CreateCriteria()
        {
            ICriteria crit = Session.CreateCriteria(typeof(Customer));
            crit.SetMaxResults(5);
            return crit.List<Customer>();
        }

        /// <summary>
        /// 结果集限定及排序
        /// </summary>
        /// <returns></returns>
        public IList<Customer> NarrowingAndOrder()
        {
            return Session.CreateCriteria(typeof(Customer))
                .Add(NHibernate.Criterion.Restrictions.Like("Firstname", "1%"))
                .AddOrder(new NHibernate.Criterion.Order("Firstname", false))
                .List<Customer>();
        }

        /// <summary>
        /// 删除对象
        /// </summary>
        /// <param name="customer"></param>
        public void DeleteCustomer(Customer customer)
        {
            Session.Delete(customer);
            Session.Flush();
        }


        #region 关联查询
        public IList<Customer> UseHQL_GetCustomersWithOrders(DateTime orderDate)
        {
            Domain.Entities.Order o = Session.Get<Domain.Entities.Order>(2);
            return Session.CreateQuery("select distinct c from Customer c inner join c.Orders o where o.OrderDate > :orderDate")
                .SetDateTime("orderDate", orderDate)
                .List<Customer>();
        }
        #endregion


        public IList<Domain.DTO> GetData()
        {
            //条件查询
            ICriteria criteria = Session.CreateCriteria(typeof(Customer)).CreateCriteria("Orders","o");
            criteria.SetProjection(Projections.ProjectionList().Add(Projections.GroupProperty("Firstname"), "Firstname").Add(Projections.GroupProperty("o.OrderDate"), "OrderDate"));
            IList<Domain.DTO> sss = criteria.SetResultTransformer(Transformers.AliasToBean(typeof(Domain.DTO))).List<Domain.DTO>();
            
            //HQL语句查询
            IList<Domain.DTO> list = Session.CreateQuery("select c.Firstname as Firstname,o.OrderDate as OrderDate from Customer c inner join c.Orders o").SetResultTransformer(Transformers.AliasToBean(typeof(Domain.DTO))).List<Domain.DTO>();
            
            //原生SQL语句查询
            return Session.CreateSQLQuery("select c.Firstname,o.OrderDate from Customer c,[Order] o where c.CustomerId=o.CustomerId").SetResultTransformer(Transformers.AliasToBean(typeof(Domain.DTO))).List<Domain.DTO>();
        }
    }
}
